USE language_classes;

INSERT INTO `student` (`last_name`,`first_name`,`middle_name`,`birthday`,`sex`,`phone_number`,`email`) VALUES ('Асирян','Александр','Камоевич','1995-02-04',1,'8-800-555-35-00','akasiryan@cs.msu.ru');
INSERT INTO `student` (`last_name`,`first_name`,`middle_name`,`birthday`,`sex`,`phone_number`,`email`) VALUES ('Иванов','Иван','Иванович','2000-01-01',1,'8-800-555-35-01','iiivanov@cs.msu.ru');
INSERT INTO `student` (`last_name`,`first_name`,`middle_name`,`birthday`,`sex`,`phone_number`,`email`) VALUES ('Петрова','Мария','Ивановна','1999-12-31',0,'8-800-555-35-02','mipetrova@cs.msu.ru');
INSERT INTO `student` (`last_name`,`first_name`,`middle_name`,`birthday`,`sex`,`phone_number`,`email`) VALUES ('Кузнецова','Дарья','Петровна','1984-01-01',0,'8-800-555-35-03','dpkuznetsova@cs.msu.ru');

INSERT INTO `language` (`name`) VALUES ('English');
INSERT INTO `language` (`name`) VALUES ('Spanish');
INSERT INTO `language` (`name`) VALUES ('Italian');
INSERT INTO `language` (`name`) VALUES ('Russian');

INSERT INTO `level` (`name`,`description`) VALUES ('Beginner','You will say and understand a few things in English.');
INSERT INTO `level` (`name`,`description`) VALUES ('Intermediate','You will speak and understand reasonably well and use basic tenses but will have problems with more complex grammar and vocabulary.');
INSERT INTO `level` (`name`,`description`) VALUES ('Advanced','You will speak and understand very well but sometimes will have problems with unfamiliar situations and vocabulary.');

INSERT INTO `teacher` (`last_name`,`first_name`,`middle_name`,`birthday`,`sex`,`phone_number`,`email`,`language_id`) VALUES ('Столяров','Андрей','Викторович','1974-12-25',1,'8-800-555-35-35','avstolyarov@cs.msu.ru',1);
INSERT INTO `teacher` (`last_name`,`first_name`,`middle_name`,`birthday`,`sex`,`phone_number`,`email`,`language_id`) VALUES ('Полосин','Алексей','Андреевич','1970-12-23',1,'8-800-555-35-36','aapolosin@cs.msu.ru',2);
INSERT INTO `teacher` (`last_name`,`first_name`,`middle_name`,`birthday`,`sex`,`phone_number`,`email`,`language_id`) VALUES ('Панфёров','Валерий','Семёнович','1950-01-02',1,'8-800-555-35-37','vspanferov@cs.msu.ru',3);
INSERT INTO `teacher` (`last_name`,`first_name`,`middle_name`,`birthday`,`sex`,`phone_number`,`email`,`language_id`) VALUES ('Гайсарян','Сергей','Суренович','1947-02-20',1,'8-800-555-35-38','ssgaisaryan@cs.msu.ru',4);

INSERT INTO `teacher_level` (`teacher_id`,`level_id`) VALUES (1,1);
INSERT INTO `teacher_level` (`teacher_id`,`level_id`) VALUES (1,3);
INSERT INTO `teacher_level` (`teacher_id`,`level_id`) VALUES (2,2);
INSERT INTO `teacher_level` (`teacher_id`,`level_id`) VALUES (3,3);
INSERT INTO `teacher_level` (`teacher_id`,`level_id`) VALUES (4,1);
INSERT INTO `teacher_level` (`teacher_id`,`level_id`) VALUES (4,2);
INSERT INTO `teacher_level` (`teacher_id`,`level_id`) VALUES (4,3);

INSERT INTO `class` (`level_id`,`teacher_id`,`start_date`,`end_date`,`description`) VALUES (1,1,'2016-10-12','2017-05-05','Good class.');
INSERT INTO `class` (`level_id`,`teacher_id`,`start_date`,`end_date`,`description`) VALUES (3,1,'2016-11-15','2017-11-30','Good class.');
INSERT INTO `class` (`level_id`,`teacher_id`,`start_date`,`end_date`,`description`) VALUES (2,2,'2017-01-01','2017-05-19','Good class.');
INSERT INTO `class` (`level_id`,`teacher_id`,`start_date`,`end_date`,`description`) VALUES (3,3,'2016-05-05','2017-02-02','Good class.');
INSERT INTO `class` (`level_id`,`teacher_id`,`start_date`,`end_date`,`description`) VALUES (3,3,'2016-11-29','2017-07-08','Good class.');
INSERT INTO `class` (`level_id`,`teacher_id`,`start_date`,`end_date`,`description`) VALUES (1,4,'2015-08-09','2016-10-13','Good class.');

INSERT INTO `class_student` (`class_id`,`student_id`) VALUES (6,1);
INSERT INTO `class_student` (`class_id`,`student_id`) VALUES (5,1);
INSERT INTO `class_student` (`class_id`,`student_id`) VALUES (2,2);
INSERT INTO `class_student` (`class_id`,`student_id`) VALUES (1,3);
INSERT INTO `class_student` (`class_id`,`student_id`) VALUES (4,4);

DELIMITER //
CREATE TRIGGER insert_class_student_check BEFORE INSERT ON class_student
FOR EACH ROW
BEGIN
	IF (SELECT end_date FROM class WHERE id = NEW.class_id) < CURDATE() THEN
		SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'This class is already finished.';
	END IF;
    
	IF (SELECT end_date FROM class
			JOIN class_student ON class.id = class_id
			WHERE student_id = NEW.student_id 
            ORDER BY end_date DESC) 
		 <= (SELECT end_date FROM class WHERE id = NEW.class_id) THEN
		SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'The student already listens the class.';
	END IF;
END//

CREATE TRIGGER update_class_student_check BEFORE UPDATE ON class_student
FOR EACH ROW
BEGIN
	IF (SELECT end_date FROM class WHERE id = NEW.class_id) < CURDATE() THEN
		SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'This class is already finished.';
	END IF;
    
	IF (SELECT end_date FROM class
			JOIN class_student ON class.id = class_id
			WHERE student_id = NEW.student_id 
            ORDER BY end_date DESC) 
		 <= (SELECT end_date FROM class WHERE id = NEW.class_id) THEN
		SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'The student already listens the class.';
	END IF;
END//
DELIMITER ;